﻿Imports MySql.Data.MySqlClient

Public Class CSemester
    Inherits CContext

    'member variables
    Private INSERT_COMMAND As String
    Private UPDATE_COMMAND As String
    Private DELETE_COMMAND As String
    Private SELECT_COMMAND As String

    Public Sub New()
        MyBase.New()
    End Sub

    Public Sub simpan(ByVal tahun As String, ByVal nama As String, ByVal tipe As String, ByVal aktif As String)
        Me.INSERT_COMMAND = "INSERT INTO TBL_SEMESTER (THN,NAMA,TIPE,AKTIF) VALUES ('" & tahun & "','" & nama & "','" & tipe & "','" & aktif & "')"
        MyBase.InsertData(INSERT_COMMAND)
    End Sub

    Public Sub rubah(ByVal tahun As String, ByVal nama As String, ByVal tipe As String, ByVal aktif As String, ByVal idSemester As Integer)
        Me.UPDATE_COMMAND = "UPDATE TBL_SEMESTER SET THN = '" & tahun & "', NAMA='" & nama & "', TIPE='" & tipe & "', AKTIF='" & aktif & "' WHERE ID=" & idSemester
        MyBase.UpdateData(UPDATE_COMMAND)
    End Sub

    Public Sub hapus(ByVal idSemester As Integer)
        'validasi
        If idSemester > 0 Then
            Me.DELETE_COMMAND = "DELETE FROM TBL_SEMESTER WHERE ID=" & idSemester
            MyBase.DeleteData(DELETE_COMMAND)
        Else
            Throw New Exception
        End If
    End Sub

    Public Function getAll() As DataTable
        Me.SELECT_COMMAND = "SELECT * FROM TBL_SEMESTER"
        Return MyBase.SelectData(SELECT_COMMAND)
    End Function

    Public Function getSemesterAktif() As DataTable
        Me.SELECT_COMMAND = "select id,thn,nama,tipe,aktif from tbl_semester where aktif = 'Y'"
        Return MyBase.SelectData(SELECT_COMMAND)
    End Function

    Public Function getAktifSemester() As tbl_semester
        MyBase.MySqlConnect.Open()
        Dim SELECT_CMD As MySqlCommand = MyBase.MySqlConnect.CreateCommand
        Dim reader As MySqlDataReader
        With SELECT_CMD
            .CommandText = "select id,thn,nama,tipe,aktif from tbl_semester where aktif = 'Y'"
            reader = .ExecuteReader
        End With
        reader.Read()
        Dim _res As New tbl_semester
        With _res
            .id = reader.Item("id")
            .nama = reader.Item("nama")
            .thn = reader.Item("thn")
            .tipe = reader.Item("tipe")
            .aktif = reader.Item("aktif")
        End With
        reader.Close()
        MyBase.MySqlConnect.Close()
        Return _res
    End Function

    Public Sub setAktifSemester(ByVal idSemester As Integer)
        Dim _TMysql As MySql.Data.MySqlClient.MySqlTransaction
        MyBase.MySqlConnect.Open()
        _TMysql = MyBase.MySqlConnect.BeginTransaction

        Try

            'hapus data lama
            UPDATE_COMMAND = "UPDATE TBL_SEMESTER SET AKTIF = 'N' WHERE ID NOT IN (" & idSemester & ")"
            Dim MYSQL_UPDATE_CMD As MySql.Data.MySqlClient.MySqlCommand = MyBase.MySqlConnect.CreateCommand
            With MYSQL_UPDATE_CMD
                .CommandText = UPDATE_COMMAND
                .Transaction = _TMysql
                .ExecuteNonQuery()
            End With

            UPDATE_COMMAND = "UPDATE TBL_SEMESTER SET AKTIF = 'Y' WHERE ID = " & idSemester
            Dim MYSQL_UPDATE2_CMD As MySql.Data.MySqlClient.MySqlCommand = MyBase.MySqlConnect.CreateCommand
            With MYSQL_UPDATE2_CMD
                .CommandText = UPDATE_COMMAND
                .Transaction = _TMysql
                .ExecuteNonQuery()
            End With

            'commit command
            _TMysql.Commit()
        Catch ex As Exception
            _TMysql.Rollback()
            MsgBox(ex.Message)
        Finally
            MyBase.MySqlConnect.Close()
        End Try
    End Sub

End Class
